﻿Public Class Form6

    Private Sub Form6_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        My.Settings.Form6Size = Me.Size
        FormPos(My.Settings.Form6Pos)
        My.Settings.Form6Pos = Me.Location
    End Sub

    Private Sub Form6_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Me.Size = My.Settings.Form6Size
        Me.Location = My.Settings.Form6Pos

        'Load order with intervention type 91 (inventory)
        GetInvetory(ComboBox1)
        GetInvetory(ComboBox3)

        'Load activ cicle count list
        Dim ConCM As OleDb.OleDbConnection
        Dim ReaderM3 As OleDb.OleDbDataReader
        Dim SQLString As String

        ConCM = New OleDb.OleDbConnection(My.Settings.M3Connect)
        ConCM.Open()
        SQLString = "select sdstnb from mittkd  group by sdstnb order by sdstnb desc"
        Dim CMDM3 As New OleDb.OleDbCommand(SQLString, ConCM)
        ReaderM3 = CMDM3.ExecuteReader()
        While ReaderM3.Read
            ComboBox2.Items.Add(ReaderM3(0).ToString)
        End While

        ConCM.Close()
    End Sub


    Function GetInvetory(ByRef Combo As ComboBox)

        'Load order with intervention type 91 (inventory)

        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader = Nothing

        Combo.Items.Clear()
        Combo.Text = ""

        cmd.CommandText = "select Auftragsnummer, TimeDate, ID from auf where störungscode = '91' and Direction = 'R' order by ID desc "
        cmd.Connection = sqlConnection1
        sqlConnection1.Open()

        reader = cmd.ExecuteReader()
        While reader.Read
            Combo.Items.Add(reader(0).ToString & "  |  " & reader(1).ToString & " | " & reader(2).ToString)
        End While
        sqlConnection1.Close()

        GetInvetory = True

    End Function




    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim ID_AUF As String
        Dim ID_ETEIL As String = ""
        Dim sVal As Object

        If ComboBox1.SelectedItem <> Nothing And ComboBox2.SelectedItem <> Nothing Then

            sVal = Split(ComboBox1.SelectedItem.ToString, "|")
            ID_AUF = Trim(sVal(2))

            AddMessage("Inventory merged ... " & ComboBox1.SelectedItem.ToString & " / " & ComboBox2.SelectedItem.ToString)

            Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
            Dim cmd As New System.Data.SqlClient.SqlCommand
            Dim reader As SqlClient.SqlDataReader = Nothing

            'Get the ETEIL ID
            cmd.CommandText = "select ID from TUER where IDM = '" & ID_AUF & "' "
            cmd.Connection = sqlConnection1
            sqlConnection1.Open()

            reader = cmd.ExecuteReader()
            If reader.Read Then
                ID_ETEIL = reader(0).ToString
            End If
            sqlConnection1.Close()

            'Merge list
            TranferInventory(ComboBox2.SelectedItem.ToString, ID_ETEIL)
            Button1.Enabled = False
        Else
            MsgBox("Selection is wrong  !!!!!")
        End If

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim sVal As Object
        Dim dDate As Date
        Dim sDate As String
        Dim sTime As String

        If ComboBox1.SelectedItem <> Nothing And ComboBox2.SelectedItem <> Nothing Then

            AddMessage("Inventory loaded in M3 ... " & ComboBox1.SelectedItem.ToString & " / " & ComboBox2.SelectedItem.ToString)

            sVal = Split(ComboBox1.SelectedItem.ToString, "|")
            dDate = Trim(sVal(1))

            sDate = dDate.ToString("yyyyMMdd")
            sTime = dDate.ToString("HHmm00")

            LoadInventory(ComboBox2.SelectedItem.ToString, sDate, sTime)
            Button2.Enabled = False
            'Change status 
            ChageStatusInventory(ComboBox2.SelectedItem.ToString, "50")
            SetInterventionCode(Trim(sVal(2)), "92")
        Else
            MsgBox("Selection is wrong  !!!!!")
        End If

    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click

        Dim ID_AUF_from As String
        Dim ID_AUF_to As String
        Dim ID_ETEIL_from As String = ""
        Dim ID_ETEIL_to As String = ""
        Dim sVal As Object

        If ComboBox1.SelectedItem <> Nothing And ComboBox3.SelectedItem <> Nothing Then

            Button3.Enabled = False
            Me.Cursor = Cursors.WaitCursor

            sVal = Split(ComboBox3.SelectedItem.ToString, "|")
            ID_AUF_from = Trim(sVal(2))
            sVal = Split(ComboBox1.SelectedItem.ToString, "|")
            ID_AUF_to = Trim(sVal(2))

            AddMessage("Inventory merged ... " & ComboBox1.SelectedItem.ToString & " / " & ComboBox3.SelectedItem.ToString)

            Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
            Dim cmd As New System.Data.SqlClient.SqlCommand
            Dim reader As SqlClient.SqlDataReader = Nothing

            'Get the ETEIL ID
            cmd.CommandText = "select ID from TUER where IDM = '" & ID_AUF_from & "' "
            cmd.Connection = sqlConnection1
            sqlConnection1.Open()

            reader = cmd.ExecuteReader()
            If reader.Read Then
                ID_ETEIL_from = reader(0).ToString
            End If
            sqlConnection1.Close()

            cmd.CommandText = "select ID from TUER where IDM = '" & ID_AUF_to & "' "

            sqlConnection1.Open()
            reader = cmd.ExecuteReader()
            If reader.Read Then
                ID_ETEIL_to = reader(0).ToString
            End If
            sqlConnection1.Close()


            'Merge list
            MergeList(ID_ETEIL_from, ID_ETEIL_to)
            Button1.Enabled = False

            SetInterventionCode(ID_AUF_from, "93")
            'Load order with intervention type 91 (inventory)
            GetInvetory(ComboBox1)
            GetInvetory(ComboBox3)

        Else
            MsgBox("Selection is wrong  !!!!!")
        End If

        Button3.Enabled = True
        Me.Cursor = Cursors.Default

    End Sub


    Function MergeList(ByVal IDm_ETEIL_from As String, ByVal IDm_ETEIL_to As String) As Boolean

        'Connection to Cadis DB Server
        Dim sqlConnection As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)
        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(My.Settings.CADISConnectionString)

        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim cmd1 As New System.Data.SqlClient.SqlCommand
        Dim ID As String = ""
        Dim ID_ETEIL As String = ""

        Dim reader1 As SqlClient.SqlDataReader = Nothing

        cmd.CommandType = System.Data.CommandType.Text
        cmd1.CommandType = System.Data.CommandType.Text
        cmd.Connection = sqlConnection
        cmd1.Connection = sqlConnection1

        sqlConnection.Open()
        sqlConnection1.Open()



        cmd1.CommandText = "SELECT ID FROM dbo.ETEIL WHERE IDm = " & IDm_ETEIL_from & ""

        reader1 = cmd1.ExecuteReader()

        While reader1.Read

            ID = Trim(reader1(0).ToString)

            ID_ETEIL = SQL_NNumber("ID_ETEIL")

            cmd.CommandText = " INSERT INTO dbo.ETEIL( ID, IDm, Artikelnummer, Seriennummer, Lagerort, Niederlassung, " & _
                                "Transaktionsmenge, Transaktionseinheit, Nachbestellungskennz, Fehlercode, Fehlersymptom, TimeDate) " & _
                                "SELECT " & ID_ETEIL & ", '" & IDm_ETEIL_to & "' , Artikelnummer, Seriennummer, Lagerort, Niederlassung, " & _
                                "Transaktionsmenge, Transaktionseinheit, Nachbestellungskennz, Fehlercode, Fehlersymptom, TimeDate " & _
                                "FROM dbo.ETEIL WHERE ID = " & ID & " "
            cmd.ExecuteNonQuery()
        End While


        sqlConnection.Close()
        sqlConnection1.Close()

        MergeList = True

    End Function





End Class